﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using TracNghiemTinHocDaiCuong.ThucThe;
using TracNghiemTinHocDaiCuong.Common;

namespace TracNghiemTinHocDaiCuong.DAO
{
    public class NguoiDungDAO
    {
        public static NguoiDung DoiTuongNguoiDung(IDataRecord read)
        {
            return new NguoiDung
            {
                MaND = Methods.ConvertNumber(read["MaND"].ToString()),
                TenTaiKhoan = Methods.ConvertString(read["TenTaiKhoan"].ToString()),
                MatKhau = Methods.ConvertString(read["MatKhau"].ToString()),
                HoVaTen = Methods.ConvertString(read["HoVaTen"].ToString()),
                NgaySinh = Methods.ConvertDateTime(read["NgaySinh"].ToString()),
                Email = Methods.ConvertString(read["Email"].ToString()),
                GioiTinh = Methods.ConvertString(read["GioiTinh"].ToString()),
                DienThoai = Methods.ConvertString(read["DienThoai"].ToString()),
                DiaChi = Methods.ConvertString(read["DiaChi"].ToString()),
                NgayDangKi = Methods.ConvertDateTime(read["NgayDangKi"].ToString())
            };
        }

        public static IList<NguoiDung> LayDanhSach()
        {
            IList<NguoiDung> dsNguoiDung = new List<NguoiDung>();
            string query = string.Format("SELECT * " +
                                            "FROM NguoiDung ORDER BY TenTaiKhoan");
            IDataReader read = SqlHelper.ExecuteReader(query);
            while (read.Read())
            {
                dsNguoiDung.Add(DoiTuongNguoiDung(read));
            }
            return dsNguoiDung;
        }

        public static NguoiDung TimKiemTheoId(int idNguoiDung)
        {
            string query = string.Format("SELECT * FROM NguoiDung WHERE MaND=@MaND");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@MaND", idNguoiDung)
            };
            IDataReader read = SqlHelper.ExecuteReader(query, parameter);
            NguoiDung nguoiDung = new NguoiDung();
            while (read.Read())
            {
                nguoiDung = DoiTuongNguoiDung(read);
            }
            return nguoiDung;
        }

        public static IList<NguoiDung> TimKiem(string tenTaiKhoan, string hoVaTen, string range)
        {
            string query = string.Format("SELECT * " +
                                            "FROM NguoiDung ");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@TenTK", tenTaiKhoan),
                new SqlParameter("@HoTen", hoVaTen)
            };
            IList<NguoiDung> danhSachNguoiDung = new List<NguoiDung>();
            if (range == "All")
            {
                IDataReader read = SqlHelper.ExecuteReader(query);
                while (read.Read())
                {
                    danhSachNguoiDung.Add(DoiTuongNguoiDung(read));
                }
                return danhSachNguoiDung;
            }
            if (tenTaiKhoan != "")
            {
                query += string.Format("WHERE TenTaiKhoan LIKE '%'+@TenTK+'%' AND " +
                "HoVaTen LIKE '%'+@HoTen+'%' ORDER BY TenTaiKhoan");
                return DanhSachNguoiDung(danhSachNguoiDung, query, parameter);
            }
            if (tenTaiKhoan == "" && range != "")
            {
                query += string.Format("WHERE TenTaiKhoan LIKE @Range AND " +
                "HoVaTen LIKE '%'+@HoTen+'%' ORDER BY TenTaiKhoan");
                var par = new Collection<SqlParameter>
                {
                    new SqlParameter("@Range", range),
                    new SqlParameter("@HoTen", hoVaTen)
                };
                return DanhSachNguoiDung(danhSachNguoiDung, query, par);
            }
            if (tenTaiKhoan == "" && range == "")
            {
                query += string.Format("WHERE HoVaTen LIKE '%'+@HoTen+'%' ORDER BY TenTaiKhoan");
                return DanhSachNguoiDung(danhSachNguoiDung, query, parameter);
            }
            else
                return DanhSachNguoiDung(danhSachNguoiDung, query, parameter);
        }    

        public static IList<NguoiDung> DanhSachNguoiDung(IList<NguoiDung> danhSachNguoiDung, 
            string query, Collection<SqlParameter> parameter)
        {
            try
            {
                IDataReader read = SqlHelper.ExecuteReader(query, parameter);
                while (read.Read())
                {
                    danhSachNguoiDung.Add(DoiTuongNguoiDung(read));
                }
                return danhSachNguoiDung;
            }
            catch
            {
                return null;
            }
        }

        public static bool Them(NguoiDung nguoiDung)
        {
            string query = string.Format("INSERT INTO NguoiDung(TenTaiKhoan, MatKhau, HoVaTen, "+
            "NgaySinh, Email, GioiTinh, DienThoai, DiaChi, NgayDangKi) " +
            "VALUES(@TenTaiKhoan, @MatKhau, @HoVaTen, @NgaySinh, @Email, @GioiTinh, @DienThoai, @DiaChi, @NgayDangKi)");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@TenTaiKhoan", nguoiDung.TenTaiKhoan),
                new SqlParameter("@MatKhau", nguoiDung.MatKhau),
                new SqlParameter("@HoVaTen", nguoiDung.HoVaTen),
                new SqlParameter("@NgaySinh", nguoiDung.NgaySinh),
                new SqlParameter("@Email", nguoiDung.Email),
                new SqlParameter("@GioiTinh", nguoiDung.GioiTinh),
                new SqlParameter("@DienThoai", nguoiDung.DienThoai),
                new SqlParameter("@DiaChi", nguoiDung.DiaChi),
                new SqlParameter("@NgayDangKi", nguoiDung.NgayDangKi)
            };
            SqlHelper.ExecuteNonQuery(query, parameter);
            return true;
        }

        public static bool Sua(NguoiDung nguoiDung)
        {
            string query = string.Format("UPDATE NguoiDung SET TenTaiKhoan=@TenTaiKhoan, MatKhau=@MatKhau, " +
            "HoVaTen=@HoVaTen, NgaySinh=@NgaySinh, " +
            "Email=@Email, GioiTinh=@GioiTinh, DienThoai=@DienThoai, DiaChi=@DiaChi, NgayDangKi=@NgayDangKi " +
            "WHERE MaND=@MaND");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@MaND", nguoiDung.MaND),
                new SqlParameter("@TenTaiKhoan", nguoiDung.TenTaiKhoan),
                new SqlParameter("@MatKhau", nguoiDung.MatKhau),
                new SqlParameter("@HoVaTen", nguoiDung.HoVaTen),
                new SqlParameter("@NgaySinh", nguoiDung.NgaySinh),
                new SqlParameter("@Email", nguoiDung.Email),
                new SqlParameter("@GioiTinh", nguoiDung.GioiTinh),
                new SqlParameter("@DienThoai", nguoiDung.DienThoai),
                new SqlParameter("@DiaChi", nguoiDung.DiaChi),
                new SqlParameter("@NgayDangKi", nguoiDung.NgayDangKi)
            };
            SqlHelper.ExecuteNonQuery(query, parameter);
            return true;
        }

        public static bool Xoa(int idNguoiDung)
        {
            string query = string.Format("DELETE FROM NguoiDung WHERE MaND=@MaND");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@MaND", idNguoiDung)
            };
            SqlHelper.ExecuteNonQuery(query, parameter);
            return true;
        }
    }
}